import plotly.offline as py
import plotly.graph_objs as go
from sqlalchemy import create_engine


# Init ####################################################################
db_password = 'djx970616'
db = create_engine("mysql+pymysql://qqbot:{}@ddns.starxy.cc:3306/csgoprice".format(db_password))
traces = []


# Graph Generate ##########################################################
def get_skins():
    skins = db.execute('select name,buff_id from IdAndName')
    skins = skins.fetchall()
    return skins


def get_dates():
    dates = db.execute('select DISTINCT datetime from History order by datetime')
    dates = dates.fetchall()
    return dates


def clear_graph(username):
    traces.clear()
    f = open('templates/user_graph/graph{}.html'.format(username), 'w')
    f.write(''.join([
        '{% extends "graphtemplate.html" %}',
        '{% block graph %}graph clear{% endblock %}']))
    return


def generate_graph(buff_id, date1, date2, username):
    left_date = min(date1, date2)
    right_date = max(date1, date2)
    time = []
    price = []
    records = db.execute(
        'select * from History where buff_id={} and datetime between {} and {} order by datetime desc'.format(
            buff_id, left_date, right_date))
    records = records.fetchall()
    skin_name = db.execute('select `name` from IdAndName where buff_id={}'.format(buff_id))
    skin_name = skin_name.fetchall()
    for j in range(len(records)):
        time.append(records[j][1])
    for j in range(len(records)):
        price.append(records[j][2])
    traces.append(go.Scatter(
        x=time,
        y=price,
        mode='lines+markers',
        name=skin_name[0][0]
    ))
    data = traces

    py.plot(data, filename='graph{}.html'.format(username), auto_open=False)
    return


def main():
    print('main run')
    return


if __name__ == '__main__':
    main()
